using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Linq;
using NHibernate.SqlCommand;


namespace Infrastucture
{
    public class PurchaseInvoiceRepository : BaseNHibernateRepository<PurchaseInvoice>
    {
        public PurchaseInvoiceRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {
        }


        public string GetMaxNumber(string kodeAwal)
        {
            string res = string.Empty;
            var lenKode = kodeAwal.Length + 10;
            var query = Session.CreateSQLQuery("Select Max(PINo) as Number from PurchaseInvoice where PINo like'" + kodeAwal + "%' AND    Len(PINo)=" + lenKode).AddScalar("Number", NHibernateUtil.String);
            var objRes = query.List()[0];
            if (objRes != null) res = objRes.ToString();
            return res;
        }


        public new IList<PurchaseInvoice> GetDataPaged(int page, int pageSize, string sortProperty = "", string sortType = "")
        {
            var Results = new List<PurchaseInvoice>();

            using (ITransaction tx = Session.BeginTransaction())
            {
                ICriteria criteria = Session.CreateCriteria<PurchaseInvoice>()
                    .CreateAlias("Supplier", "sp", JoinType.InnerJoin)
                    .CreateAlias("Currency", "c", JoinType.LeftOuterJoin)
                    .CreateAlias("Btbs", "btbs", JoinType.LeftOuterJoin);


                if (sortType != "")
                {
                    if (sortType.ToUpper() == "ASC")
                        criteria.AddOrder(new Order(sortProperty, true));
                    else
                        criteria.AddOrder(new Order(sortProperty, false));
                }

                criteria.SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize);

                ICriteria criteriacount =
                    Session.CreateCriteria<PurchaseInvoice>()
                    .SetProjection(Projections.RowCountInt64());

                IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriacount).List();

                foreach (object o in (IList)Res[0])
                {
                    var po = (PurchaseInvoice)o;
                    Results.Add(po);
                }

                RecordCount = (long)((IList)Res[1])[0];
                tx.Commit();
            }

            return Results;
        }


        public override IList<PurchaseInvoice> GetDataPaged(Dictionary<string, object> Criteria, string expression, int page, int pageSize)
        {
            var Results = new List<PurchaseInvoice>();


            using (ITransaction tx = Session.BeginTransaction())
            {
                ICriteria criteria = Session.CreateCriteria<PurchaseInvoice>()
                    .CreateAlias("Supplier", "sp", JoinType.InnerJoin)
                    .CreateAlias("Currency", "c", JoinType.LeftOuterJoin)
                    .CreateAlias("Btbs", "btbs", JoinType.LeftOuterJoin);
                criteria.SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize);

                var filter = new List<string>();
                int i = 0;
                foreach (var criterion in Criteria)
                    if (!typeof(PurchaseInvoice).GetProperties().Any(
                            x => x.Name == criterion.Key.Split(new[] { "." }, StringSplitOptions.None)[0]))
                        filter.Add(criterion.Key);

                filter.ForEach((x) => { Criteria.Remove(x); });

                dynamic exp = null;
                if (expression == "AND")
                    exp = Expression.Conjunction();

                else
                    exp = Expression.Disjunction();

                foreach (var criterion in Criteria)
                {
                    if (criterion.Value == null) continue;
                    if (criterion.Value.GetType() == typeof(string))
                        exp.Add(Expression.Like(criterion.Key, criterion.Value.ToString(), MatchMode.Anywhere));
                    else
                        exp.Add(Expression.Eq(criterion.Key, criterion.Value));
                    i += 1;
                }
                criteria.Add(exp);
                ICriteria criteriacount =
                    Session.CreateCriteria<PurchaseInvoice>()
                    .SetProjection(Projections.RowCountInt64());

                IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriacount.Add(exp)).List();

                foreach (object o in (IList)Res[0])
                {
                    var po = (PurchaseInvoice)o;
                    Results.Add(po);
                }

                RecordCount = (long)((IList)Res[1])[0];
                tx.Commit();
            }

            return Results;
        }
       

        public void UpdateStatus(string PINo, int Status)
        {
            ISQLQuery command =
            Session.CreateSQLQuery("Update PurchaseInvoice set Status=" + Status + " Where PINo='" + PINo + "'");
            int res = command.ExecuteUpdate();
        }


        public PurchaseInvoice GetPurchaseInvoiceByBtb(string noBTB)
        {
           var query =  Session.CreateSQLQuery("Select * from PurchaseInvoice where NoBTB='" + noBTB + "'");
            return query.List<PurchaseInvoice>().FirstOrDefault();
        }


    }
}